﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;

namespace MacViet.DataBaseAPI
{
   public class SQLHelper
    {
       public string _strConnection = System.Configuration.ConfigurationSettings.AppSettings["CurrentDBNailsConnect"].ToString();
        private SqlConnection _sqlConnection;
        private SqlCommand _sqlCommand;
        private string _myError;
        public SQLHelper()
       { }
        public string MyError
        {
            get { return _myError; }
            set { _myError = value; }
        }
        public string ConnectionString
        {
            get { return _strConnection; }
            set { _strConnection = value; }
        }
       
        #region IDataLayer Members

        public void CloseConnect()
        {
            _sqlConnection.Close();
        }

        public void DisposeConnect()
        {
            _sqlConnection.Dispose();
        }

        public int ExecuteNoneQuery(string strSQL, params object[] paramValues)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            try
            {
                foreach (SqlParameter var in paramValues)
                {
                    _sqlCommand.Parameters.Add(var);
                }
               return _sqlCommand.ExecuteNonQuery();
                    
                   
            }

            catch(Exception ex)
            {
                ex.ToString();
                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return 0;
        }
        public int ExecuteNoneQuery(string strSQL,CommandType commandType)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = commandType;
            _sqlCommand.Connection = _sqlConnection;
            try
            {               
                return _sqlCommand.ExecuteNonQuery();


            }

            catch (Exception ex)
            {
                ex.ToString();
                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return 0;
        }
        public int ExecuteNoneQuery(string strSQL, CommandType commandType, params object[] paramValues)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = commandType;
            _sqlCommand.Connection = _sqlConnection;
            try
            {
                foreach (SqlParameter var in paramValues)
                {
                    _sqlCommand.Parameters.Add(var);
                }
                return _sqlCommand.ExecuteNonQuery();


            }

            catch (Exception ex)
            {
                ex.ToString();
                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return 0;
        } 
        public int ExecuteNoneQuery(string strSQL)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.Text;
            _sqlCommand.Connection = _sqlConnection;
            try
            {
               return _sqlCommand.ExecuteNonQuery();
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return 0;
        }

        public object ExecuteScalar(string strSQL, params object[] paramValues)
        {
            object objValue = null;
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            try
            {
                foreach (SqlParameter var in paramValues)
                {
                    _sqlCommand.Parameters.Add(var);
                }
                objValue = _sqlCommand.ExecuteScalar();
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return objValue;
        }

        public object ExecuteScalar(string strSQL)
        {
            object objValue = null;
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            try
            {
                objValue = _sqlCommand.ExecuteScalar();
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return objValue;
        }

        public DataTable executeReader(string strSQL, params object[] paramValues)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            SqlDataReader _sqlDataReader = null;
            DataTable dt = new DataTable();
            try
            {
                foreach (SqlParameter var in paramValues)
                {
                    _sqlCommand.Parameters.Add(var);
                }
                _sqlDataReader = _sqlCommand.ExecuteReader();
                dt.Load(_sqlDataReader);
                if (dt.Rows.Count > 0)
                    return dt;
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally { CloseConnect(); DisposeConnect(); }
            return dt=null;
        }

        public DataTable executeReader(string strSQL)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            SqlDataReader _sqlDataReader = null;
            DataTable dt = new DataTable();
            try
            {
                _sqlDataReader = _sqlCommand.ExecuteReader();
                dt.Load(_sqlDataReader);
                if (dt.Rows.Count > 0)
                    return dt;
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally { CloseConnect(); DisposeConnect(); }
            return dt=null;
        }

        public DataSet GetDataSet(string strSQL, params object[] paramValues)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);
            DataSet ds = new DataSet();
            try
            {
                foreach (SqlParameter var in paramValues)
                {
                    _sqlCommand.Parameters.Add(var);
                }
                _sqlDataAdapter.Fill(ds);

                return ds;
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return ds = null;
        }
        public DataTable executeDataTable(string strSQL, params object[] paramValues)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);
            DataSet ds = new DataSet();
            try
            {
                foreach (SqlParameter var in paramValues)
                {
                    _sqlCommand.Parameters.Add(var);
                }
                _sqlDataAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch(Exception ex)
            {
                ex.ToString();
                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return ds.Tables[0]; 
        }
        public DataTable executeDataTable(string strSQL)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);
            DataSet ds = new DataSet();
            try
            {

                _sqlDataAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return ds.Tables[0];
        }
        public DataSet GetDataSet(string strSQL)
        {
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter(_sqlCommand);
            DataSet ds = new DataSet();
            try
            {

                _sqlDataAdapter.Fill(ds);

                return ds;
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return ds = null;
        }

        public byte[] GetObjByID(string strSQL, params object[] paramValues)
        {
            byte[] images = null;
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            try
            {
                foreach (SqlParameter var in paramValues)
                {
                    _sqlCommand.Parameters.Add(var);
                }
                images = _sqlCommand.ExecuteScalar() as byte[];
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return images;
        }

        public byte[] GetObjByID(string strSQL)
        {
            byte[] images = null;
            OpenConnect();
            _sqlCommand = new SqlCommand();
            _sqlCommand.CommandText = strSQL;
            _sqlCommand.CommandType = CommandType.StoredProcedure;
            _sqlCommand.Connection = _sqlConnection;
            try
            {
                images = _sqlCommand.ExecuteScalar() as byte[];
            }
            catch
            {

                MyError = "Connect error!";
            }
            finally
            {
                CloseConnect();
                DisposeConnect();
            }
            return images;
        }
        public string GetEncryString(string s)
        {
            string strEncryt = string.Empty;
            byte[] byt = SHA1.Create().ComputeHash(Encoding.Default.GetBytes(s));
            for (int i = 0; i < byt.Length; i++)
            {
                strEncryt += byt[i].ToString("X");
            }
            return strEncryt;
        }
        public void OpenConnect()
        {
            _sqlConnection = new SqlConnection(ConnectionString);
            _sqlConnection.Open();
        }

        #endregion


       
    }
}
